#  python3 sqllite3
import sqlite3 as db
import os
import '.\lib\mySequence'      

class mySequence:
    """docstring for mySequence"""
    def __init__(self, path):
        self.path = path
        self.conn = self.getConnect()
        self.cu = self.getCursor()
        
    def getConnect(self):
        path = self.path
        '''
    返回数据库连接，如果数据库文件不存在返回内存数据库连接
        '''
        if os.path.exists(path) and os.path.isfile(path):
            conn = db.connect(path)
        else:
            path1 = os.path.split(path)[0]
            if os.path.exists(path1):
                conn = db.connect(path)
            else:
                os.makedirs(path1)
                conn = db.connect(path)
        return conn

    def getCursor(self):
        '''
    返回游标 如果conn不存在返回 内容数据库游标
        '''
        conn = self.conn
        if conn is not None:
            cu= conn.cursor()
        else:
            print("数据库连接不存在，建立内存数据库")
            cu = getConnect('').cursor()

        return cu
        
    def createMySequenceTable(cu):
        createMySequenceTableSql = '''
        create table `mySequence`(
            id int not null,    
            name varchar(50) not null,
            for_table varchar(50) not null,
            value int not null,
            step int not null,
            PRIMARY KEY (`id`)
            )
        '''
        insertFristSequence= 'insert into mySequence values(:id, :name, :for_table, :value, :step)'
        cu.execute(createMySequenceTableSql)
        cu.execute(insertFristSequence,{'id':1, 'name':'mySequence_id_seq', 'for_table':'mySequence', 'value':1, 'step':1 })
        cu.execute('commit')
        return 1

    def  mySequenceNextValue(cu,name):
        getValueSql = 'select step, value from mySequence where name = ?'
        updateValueSql = 'update mySequence set value = :value where name = :name'
        step, nextValue = cu.execute(getValueSql, name)
        nextValue += step
        cu.execute(updateValueSql, {'name':name, 'value':value})
        cu.execute('commit')
        return nextValue

    def mySequenceCurrentValue(cu, name):
        getValueSql = 'select value from mySequence where name = ?'
        return cu.execute(getValueSql, name)

    def createSequence(cu,seq_name,table_name):
        createSequenceSql='insert into mySequence values(:id, :name, :for_table, :value, :step)'
        cu.execute(createSequenceSql,{'id':mySequenceCurrentValue(c,mySequence), 'name':seq_name, 'for_table':table_name, 'value':1, 'step':1 })
